package edu.nf.dao;

import edu.nf.entity.Users;
import edu.nf.utils.DBUtil;
import edu.nf.utils.ResultSetHandler;
import edu.nf.utils.SQLExecutor;
import edu.nf.utils.handler.*;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class UserDao {

    public int save(Users user) {
        int row = 0;
        String sql = "INSERT INTO USER_INFO(NICK_NAME,ACCOUNT,PASSWORD) VALUES(?,?,?)";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        se.beginTransaction();
        try {
            row = se.executeUpdate(sql, user.getNickName(), user.getAccount(), user.getPassWord());
            se.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            se.rollback();
        }

        return row;
    }

    public Users findUserById(int id) {
        String sql = "SELECT * FROM USER_INFO WHERE USER_ID=?";
        Users user = null;
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        ResultSetHandler<Users> handler = new BeanHandler<Users>(Users.class);
        try {
            user = se.executeQuery(sql, handler, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public List<Users> findUsers() {
        String sql = "SELECT * FROM USER_INFO";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        ResultSetHandler<List<Users>> handler = new BeanListHandler<Users>(Users.class);

        List<Users> list = null;
        try {
            list = se.executeQuery(sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public Object[] findUserById2(int userId) {
        String sql = "SELECT * FROM USER_INFO WHERE USER_ID=?";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        ResultSetHandler<Object[]> handler = new ArrayHandler();
        Object[] user = null;
        try {
            user = se.executeQuery(sql, handler, userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public Map<String, Object> findUserById3(int userId) {
        String sql = "SELECT * FROM USER_INFO WHERE USER_ID=?";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        ResultSetHandler<Map<String, Object>> handler = new MapHandler();
        Map<String, Object> map = null;
        try {
            map = se.executeQuery(sql, handler, userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    public int count() {
        String sql = "SELECT COUNT(*) FROM USER_INFO ";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        ResultSetHandler<Integer> handler = new ColumnHandler<Integer>(1, Integer.class);
        int count = 0;
        try {
            count = se.executeQuery(sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public List<Object[]> findUsers2() {
        String sql = "SELECT * FROM USER_INFO";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        ResultSetHandler<List<Object[]>> handler = new ArrayLlistHandler();
        List<Object[]> values = null;
        try {
            values = se.executeQuery(sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return values;
    }

    public List<Map<String, Object>> findUsers3() {
        String sql = "SELECT * FROM USER_INFO";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        ResultSetHandler<List<Map<String, Object>>> handler = new MapListHandler();
        List<Map<String, Object>> values = null;
        try {
            values = se.executeQuery(sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return values;
    }

    /**
     * 批量添加
     *
     * @param users
     * @return
     */
    public int[] insertBatch(List<Users> users) {
        List<List<Object>> params = new ArrayList<List<Object>>();
        int[] results = null;
        String sql = "insert into user_info(NICK_NAME,ACCOUNT,PASSWORD,CREATE_TIME,age) values(?,?,?,?,?)";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        for (Users user : users) {
            params.add(paramPackage(
                    user.getNickName(),
                    user.getAccount(),
                    user.getPassWord(),
                    user.getCreateTime(),
                    user.getAge()));
        }
        try {
            se.beginTransaction();
            results = se.executeBatch(sql, listToArray(params));
            se.commit();
        } catch (SQLException e) {
            se.rollback();
            throw new RuntimeException("批量添加失败！");
        }
        return results;
    }


    /**
     * 批量删除
     *
     * @param ids
     * @return
     */
    public int[] deleteBatch(int[] ids) {
        List<List<Object>> params = new ArrayList<List<Object>>();
        String sql = "delete from user_info where user_id=?";
        SQLExecutor se = new SQLExecutor(DBUtil.getConnection());
        int[] results = null;
        for (int id : ids) {
            params.add(paramPackage(id));
        }
        try {
            se.beginTransaction();
            results = se.executeBatch(sql, listToArray(params));
            se.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            se.rollback();
        }
        return results;
    }

    /**
     * 集合转数组，数组的二维必须是对称的
     *
     * @param params
     * @return
     */
    private static Object[][] listToArray(List<List<Object>> params) {
        if (params == null) {
            return null;
        }
        int row = params.size();
        int col = params.size() == 0 ? 0 : params.get(0).size();
        Object[][] values = new Object[row][col];
        for (int i = 0; i < row; i++) {
            for (int j = 0; j < col; j++) {
                values[i][j] = params.get(i).get(j);
            }
        }
        return values;
    }

    /**
     * 参数装箱
     *
     * @param args
     * @return
     */
    private List<Object> paramPackage(Object... args) {
        List<Object> list = new ArrayList<Object>();
        for (Object arg : args) {
            list.add(arg);
        }
        return list;
    }

    public static void main(String[] args) {
       new UserDao().count();
    }
}
